package util

import (
	"database/sql"
	"github.com/astaxie/beego/orm"
	"math"
	"nspel/entity"
	"nspel/nspel"
	"strconv"
	"strings"
	"time"
)

type ImportMsg struct {
	RetCode  int              `json:"retCode"`
	Insert   int              `json:"insert"`
	Update   int              `json:"update"`
	Result   bool             `json:"result"`
	ErrorMsg []ImportErrorMsg `json:"errorMsg"`
}

type PhysicalImportMsg struct {
	RetCode       int64            `json:"retCode"`
	ValidNum      int64            `json:"valid"`
	InsertStudent int64            `json:"insertStudent"`
	UpdateStudent int64            `json:"updateStudent"`
	Insert        int64            `json:"insert"`
	Update        int64            `json:"update"`
	ExemptionNum  int64            `json:"exemption"`
	MissingNum    int64            `json:"missing"`
	ErrorMsg      []ImportErrorMsg `json:"errorMsg"`
	Result        bool             `json:"result"`
}

type ImportErrorMsg struct {
	At     string `json:"at"`
	Reason string `json:"reason"`
}

var areaColumn []string
var schoolColumn []string
var studentColumn []string
var primaryPhysicalColumn []string
var highPhysicalColumn []string
var physicalColumn []string

func init() {
	areaColumn = []string{"省份", "城市", "实验区名称", "负责人", "联系电话", "邮箱", "联系地址", "学校总数(所)", "实验区颁牌时间", "示范区颁牌", "加入年份"}
	schoolColumn = []string{"国籍", "省/直辖市", "市/区", "区/县", "实验区", "学校名称", "学校编码", "编码", "学生数量", "学制", "校长姓名", "校长电话", "校长邮箱", "联系人", "联系电话", "邮箱", "申请表", "申请日期", "学校网址", "学校公众号", "是否授实验校牌匾", "实验校颁牌时间", "是否示范校", "示范校颁牌时间", "联系地址"}
	studentColumn = []string{"学籍号", "学校编码", "年级编码", "班级编码", "姓名", "性别", "出生日期", "身份证号", "民族", "家庭住址", "家长电话", "学号"}
	primaryPhysicalColumn = []string{"年级编号", "班级编号", "学籍号", "民族代码", "姓名", "性别", "是否免测", "身高", "体重", "肺活量", "50米跑", "坐位体前屈", "50米×8往返跑", "一分钟仰卧起坐", "一分钟跳绳"}
	highPhysicalColumn = []string{"年级编号", "班级编号", "学籍号", "民族代码", "姓名", "性别", "是否免测", "身高", "体重", "肺活量", "50米跑", "立定跳远", "坐位体前屈", "800米跑", "1000米跑", "一分钟仰卧起坐", "引体向上"}
	physicalColumn = []string{"年级编号", "班级编号", "学籍号", "民族代码", "姓名", "性别", "出生日期", "身份证号", "是否补测", "是否免测", "身高", "体重", "肺活量", "50米跑", "立定跳远", "坐位体前屈", "50米×8往返跑", "800米跑", "1000米跑", "1分钟仰卧起坐", "1分钟跳绳", "引体向上", "体脂百分比", "握力", "反应能力", "平衡能力", "纵跳", "语文", "数学", "英语", "政治", "历史", "地理", "物理", "化学", "生物", "科学"}
}

func ValidateAreaImport(excelData *[][]string) (importMsg ImportMsg, create []*entity.Area, update []*entity.Area, err error) {
	errMsg, column := validateExcelColumn(excelData, 0, &areaColumn)
	if len(errMsg) != 0 {
		importMsg.ErrorMsg = append(importMsg.ErrorMsg, errMsg...)
		return
	}
	sameMap := make(map[interface{}]int)
	phoneMap := make(map[interface{}]int)
	for i := 1; i < len(*excelData); i++ {
		area := new(entity.Area)
		var errMsg []ImportErrorMsg
		area.ProvinceId, area.CountryId, err = validateProvince(excelData, i, column["省份"], &errMsg, "省份")
		if err != nil {
			return
		}
		area.CityId, err = validateCityWithProvince(excelData, i, column["城市"], &errMsg, area.ProvinceId, (*excelData)[i][column["省份"]], "城市", "省份")
		if err != nil {
			return
		}
		area.Id, area.UserId, err = validateAreaIsNew(excelData, i, column["实验区名称"], &errMsg, area.CityId, "实验区名称")
		if err != nil {
			return
		}
		area.Name = (*excelData)[i][column["实验区名称"]]
		area.Contact = validateNullString(excelData, i, column["负责人"], &errMsg, 0, 255, "负责人")
		area.ContactPhone = validateNullPhone(excelData, i, column["联系电话"], &errMsg, 0, 255, "联系电话")
		area.ContactEmail = validateNullEmail(excelData, i, column["邮箱"], &errMsg, 0, 255, "邮箱")
		area.ContactAddress = validateNullString(excelData, i, column["联系地址"], &errMsg, 0, 255, "联系地址")
		area.AllSchoolNum = validateInt(excelData, i, column["学校总数(所)"], &errMsg, 0, math.MaxInt32, false, 0, "学校总数(所)")
		area.ExperimentAt = validateDate(excelData, i, column["实验区颁牌时间"], &errMsg, 2000, time.Now().Year(), false, "实验区颁牌时间")
		area.StandoutAt = validateDate(excelData, i, column["示范区颁牌"], &errMsg, 2000, time.Now().Year(), false, "示范区颁牌")
		area.JoinYear = validateNullInt(excelData, i, column["加入年份"], &errMsg, 2000, int64(time.Now().Year()), true, 0, "加入年份")
		area.IsExperiment = true
		if len(errMsg) == 0 {
			err = validateAreaUser(excelData, i, column["联系电话"], &errMsg, entity.ManagerAuthority.Area, "联系电话", area.Id, &phoneMap)
			if err != nil {
				return
			}
		}
		validateSame(&sameMap, entity.Area{CountryId: area.CountryId, ProvinceId: area.ProvinceId, CityId: area.CityId, Name: area.Name}, i, &errMsg)
		if len(errMsg) != 0 {
			importMsg.ErrorMsg = append(importMsg.ErrorMsg, errMsg...)
			continue
		}
		if area.Id == 0 {
			create = append(create, area)
		} else {
			update = append(update, area)
		}
	}
	if len(importMsg.ErrorMsg) == 0 {
		importMsg.Result = true
	}
	importMsg.Insert = len(create)
	importMsg.Update = len(update)
	return
}

func AreaImport(create *[]*entity.Area, update *[]*entity.Area) error {
	for i := 0; i < len(*create); i++ {
		err := (*(*create)[i]).ImportInsert()
		if err != nil {
			return err
		}
	}
	for i := 0; i < len(*update); i++ {
		err := (*(*update)[i]).ImportUpdate()
		if err != nil {
			return err
		}
	}
	nspel.RedisKeyDelete("area")
	return nil
}

func ValidateSchoolImport(excelData *[][]string) (importMsg ImportMsg, create []*entity.School, update []*entity.School, experimentAreaMap map[int64]byte, err error) {
	errMsg, column := validateExcelColumn(excelData, 0, &schoolColumn)
	if len(errMsg) != 0 {
		importMsg.ErrorMsg = append(importMsg.ErrorMsg, errMsg...)
		return
	}
	sameMap := make(map[interface{}]int)
	experimentAreaMap = make(map[int64]byte)
	phoneMap := make(map[interface{}]int)
	for i := 1; i < len(*excelData); i++ {
		school := new(entity.School)
		var errMsg []ImportErrorMsg
		school.CountryId, err = validateCountry(excelData, i, column["国籍"], &errMsg, "国籍")
		if err != nil {
			return
		}
		school.ProvinceId, err = validateProvinceWithCountry(excelData, i, column["省/直辖市"], &errMsg, school.CountryId, (*excelData)[i][column["国籍"]], "省/直辖市", "国籍")
		if err != nil {
			return
		}
		school.CityId, err = validateCityWithProvince(excelData, i, column["市/区"], &errMsg, school.ProvinceId, (*excelData)[i][column["省/直辖市"]], "市/区", "省/直辖市")
		if err != nil {
			return
		}
		school.AreaId, err = validateAreaWithCity(excelData, i, column["区/县"], &errMsg, school.CityId, (*excelData)[i][column["市/区"]], "区/县", "市/区")
		if err != nil {
			return
		}
		school.ExperimentAreaId, err = validateAreaWithCity(excelData, i, column["实验区"], &errMsg, school.CityId, (*excelData)[i][column["市/区"]], "实验区", "市/区")
		if err != nil {
			return
		}
		school.Name = validateString(excelData, i, column["学校名称"], &errMsg, 1, 255, "学校名称")
		school.Id, school.UserId, err = validateSchoolIsNew(excelData, i, column["学校编码"], &errMsg, "学校编码")
		if err != nil {
			return
		}
		school.SchoolCode = (*excelData)[i][column["学校编码"]]
		school.Code = validateNullString(excelData, i, column["编码"], &errMsg, 0, 255, "编码")
		school.StudentNum = validateInt(excelData, i, column["学生数量"], &errMsg, 0, math.MaxInt32, false, 0, "学生数量")
		school.SchoolType = validateEnum(excelData, i, column["学制"], &errMsg, true, byte(0), entity.SchoolTypeMapName, "学制").(byte)
		school.HeadMaster = validateNullString(excelData, i, column["校长姓名"], &errMsg, 0, 255, "校长姓名")
		school.HeadMasterPhone = validateNullPhone(excelData, i, column["校长电话"], &errMsg, 0, 255, "校长电话")
		school.HeadMasterEmail = validateNullEmail(excelData, i, column["校长邮箱"], &errMsg, 0, 255, "校长邮箱")
		school.Contact = validateNullString(excelData, i, column["联系人"], &errMsg, 0, 255, "联系人")
		school.ContactPhone = validateNullPhone(excelData, i, column["联系电话"], &errMsg, 0, 255, "联系电话")
		school.ContactEmail = validateNullEmail(excelData, i, column["邮箱"], &errMsg, 0, 255, "邮箱")
		school.HasApply = validateEnum(excelData, i, column["申请表"], &errMsg, false, false, map[string]interface{}{"有": true, "无": false}, "申请表").(bool)
		school.JoinAt = validateDate(excelData, i, column["申请日期"], &errMsg, 2000, time.Now().Year(), false, "申请日期")
		school.Homepage = validateNullString(excelData, i, column["学校网址"], &errMsg, 0, 255, "学校网址")
		school.OfficialAccounts = validateNullString(excelData, i, column["学校公众号"], &errMsg, 0, 255, "学校公众号")
		school.IsExperiment = validateEnum(excelData, i, column["是否授实验校牌匾"], &errMsg, false, false, map[string]interface{}{"是": true, "否": false}, "是否授实验校牌匾").(bool)
		school.ExperimentAt = validateDate(excelData, i, column["实验校颁牌时间"], &errMsg, 2000, time.Now().Year(), false, "实验校颁牌时间")
		school.IsStandout = validateEnum(excelData, i, column["是否示范校"], &errMsg, false, false, map[string]interface{}{"是": true, "否": false}, "是否示范校").(bool)
		school.StandoutAt = validateDate(excelData, i, column["示范校颁牌时间"], &errMsg, 2000, time.Now().Year(), false, "示范校颁牌时间")
		school.ContactAddress = validateNullString(excelData, i, column["联系地址"], &errMsg, 0, 255, "联系地址")
		if len(errMsg) == 0 {
			err = validateSchoolUser(excelData, i, column["联系电话"], &errMsg, entity.ManagerAuthority.School, "联系电话", school.Id, &phoneMap)
			if err != nil {
				return
			}
		}
		validateSame(&sameMap, school.SchoolCode, i, &errMsg)
		if len(errMsg) != 0 {
			importMsg.ErrorMsg = append(importMsg.ErrorMsg, errMsg...)
			continue
		}
		experimentAreaMap[school.ExperimentAreaId] = 0
		if school.Id == 0 {
			create = append(create, school)
		} else {
			update = append(update, school)
		}
	}
	if len(importMsg.ErrorMsg) == 0 {
		importMsg.Result = true
	}
	importMsg.Insert = len(create)
	importMsg.Update = len(update)
	return
}

func SchoolImport(create *[]*entity.School, update *[]*entity.School, experimentAreaMap *map[int64]byte) error {
	for i := 0; i < len(*create); i++ {
		err := (*(*create)[i]).ImportInsert()
		if err != nil {
			return err
		}
	}
	for i := 0; i < len(*update); i++ {
		err := (*(*update)[i]).ImportUpdate()
		if err != nil {
			return err
		}
	}
	for k := range *experimentAreaMap {
		area := new(entity.Area)
		area.Id = k
		school := new(entity.School)
		authorizedSchoolNum, err := school.SelectCountByExperimentAreaId(k)
		if err != nil {
			return err
		}
		standoutSchoolNum, err := school.SelectCountByExperimentAreaIdIsStandout(k, true)
		if err != nil {
			return err
		}
		area.AuthorizedSchoolNum = authorizedSchoolNum
		area.StandoutSchoolNum = standoutSchoolNum
		err = area.Update("authorized_school_num", "standout_school_num")
		if err != nil {
			return err
		}
	}
	nspel.RedisKeyDelete("school")
	return nil
}

/*
func ValidateStudentImport (excelData *[][]string) (importMsg ImportMsg, create []*entity.Student, update []*entity.Student, schoolMap map[int64]byte, err error) {
	errMsg, column := validateExcelColumn(excelData, 0, &studentColumn)
	if len(errMsg) != 0 {
		importMsg.ErrorMsg = append(importMsg.ErrorMsg, errMsg...)
		return
	}
	sameMap := make(map[interface{}]int64)
	schoolMap = make(map[int64]byte)
	for i := 1; i < len(*excelData); i++ {
		student := new(entity.Student)
		var errMsg []ImportErrorMsg
		var schoolType byte
		student.Id, student.Password, err = validateStudentIsNew(excelData, i, column["学籍号"], &errMsg, "学籍号")
		if err != nil {
			return
		}
		student.SchoolRollId = (*excelData)[i][column["学籍号"]]
		student.SchoolId, student.CountryId, student.ProvinceId, student.CityId, student.AreaId, student.ExperimentAreaId, schoolType, err = validateSchoolCode(excelData, i, column["学校编码"], &errMsg, "学校编码")
		if err != nil {
			return
		}
		student.CurrentGrade = validateCurrentGrade(excelData, i, column["年级编码"], &errMsg, "年级编码", schoolType)
		student.CurrentClass = validateCurrentClass(excelData, i, column["班级编码"], &errMsg, "班级编码", time.Now().Year(), 2000, schoolType)
		student.Name = validateString(excelData, i, column["姓名"], &errMsg, 1, 255, "姓名")
		student.Sex = validateEnum(excelData, i, column["性别"], &errMsg, true, byte(1), map[string]interface{}{"男": byte(1), "女": byte(2)}, "性别").(byte)
		student.Birthday = validateDate(excelData, i, column["出生日期"], &errMsg, 2000, time.Now().Year(), false, "出生日期")
		student.IdentityId = validateString(excelData, i, column["身份证号"], &errMsg, 0, 18, "身份证号")
		student.Nation = strconv.Itoa(validateInt(excelData, i, column["民族"], &errMsg, 1, 98, true, 1, "民族"))
		student.Address = validateString(excelData, i, column["家庭住址"], &errMsg, 0, 255, "家庭住址")
		student.Phone = validatePhone(excelData, i, column["家长电话"], &errMsg, 0, 255, "家长电话")
		student.StudentId = validateString(excelData, i, column["学号"], &errMsg, 1, 255, "学号")
		validateSame(&sameMap, student.SchoolRollId, i, &errMsg)
		if len(errMsg) != 0 {
			importMsg.ErrorMsg = append(importMsg.ErrorMsg, errMsg...)
			continue
		}
		schoolMap[student.SchoolId] = 0
		if student.Id == 0 {
			create = append(create, student)
		} else {
			update = append(update, student)
		}
	}
	if len(importMsg.ErrorMsg) == 0 {
		importMsg.Result = true
	}
	importMsg.Insert = len(create)
	importMsg.Update = len(update)
	return
}
*/
func StudentImport(create *[]*entity.Student, update *[]*entity.Student, schoolMap *map[int64]byte) error {
	for i := 0; i < len(*create); i++ {
		err := (*(*create)[i]).ImportInsert()
		if err != nil {
			return err
		}
	}
	for i := 0; i < len(*update); i++ {
		err := (*(*update)[i]).ImportUpdate()
		if err != nil {
			return err
		}
	}
	/*
		for k := range *schoolMap {
			student := new(entity.Student)
			studentNum, err := student.SelectCountBySchoolId(k)
			if err != nil {
				return err
			}
			school := new(entity.School)
			school.Id = k
			school.StudentNum = studentNum
			err = school.Update("student_num")
			if err != nil {
				return err
			}
		}*/
	nspel.RedisKeyDelete("student")
	return nil
}

func ValidatePhysicalImport(excelData *[][]string, school *entity.School, measureDate time.Time) (importMsg PhysicalImportMsg, createStudent []*entity.Student, createScore []*entity.Score, updateStudent []*entity.Student, create []*entity.Score, update []*entity.Score, err error) {
	//schoolTypeStr := entity.SchoolTypeMapCode[schoolType]
	var column map[string]int
	var errMsg []ImportErrorMsg
	errMsg, column = validateExcelColumn(excelData, 0, &physicalColumn)
	if len(errMsg) != 0 {
		importMsg.ErrorMsg = append(importMsg.ErrorMsg, errMsg...)
		return
	}
	sameMap := make(map[interface{}]int)
	for i := 1; i < len(*excelData); i++ {
		student := new(entity.Student)
		score := new(entity.Score)
		var errMsg []ImportErrorMsg
		student.CountryId = school.CountryId
		student.ProvinceId = school.ProvinceId
		student.CityId = school.CityId
		student.AreaId = school.AreaId
		student.ExperimentAreaId = school.ExperimentAreaId
		student.SchoolId = school.Id
		score.CountryId = school.CountryId
		score.ProvinceId = school.ProvinceId
		score.CityId = school.CityId
		score.AreaId = school.AreaId
		score.SchoolId = school.Id
		score.ExperimentAreaId = school.ExperimentAreaId
		student.CurrentGrade = validateCurrentGrade(excelData, i, column["年级编号"], &errMsg, "年级编号", school.SchoolType)
		score.Grade = (*excelData)[i][column["年级编号"]]
		student.CurrentClass = validateCurrentClass(excelData, i, column["班级编号"], &errMsg, "班级编号", time.Now().Year(), 2000, school.SchoolType)
		score.Class = (*excelData)[i][column["班级编号"]]
		(*excelData)[i][column["学籍号"]] = strings.Replace((*excelData)[i][column["学籍号"]], "\t", "", -1)
		student.Id, student.Password, err = validateStudentIsNew(excelData, i, column["学籍号"], &errMsg, "学籍号")
		if err != nil {
			return
		}
		student.SchoolRollId = (*excelData)[i][column["学籍号"]]
		score.SchoolRollId = (*excelData)[i][column["学籍号"]]
		score.StudentId = student.Id
		score.Id, err = validateScoreIsNew(excelData, i, column["学籍号"], measureDate)
		if err != nil {
			return
		}
		student.Nation = strconv.FormatInt(validateInt(excelData, i, column["民族代码"], &errMsg, 1, 98, true, 1, "民族代码"), 10)
		score.Nation = student.Nation
		student.Name = validateString(excelData, i, column["姓名"], &errMsg, 1, 255, "姓名")
		student.Sex = byte(validateInt(excelData, i, column["性别"], &errMsg, 1, 2, true, 1, "性别"))
		score.Sex = student.Sex
		student.Birthday = validateDate(excelData, i, column["出生日期"], &errMsg, 1990, time.Now().Year(), false, "出生日期")
		student.IdentityId = validateIdentityId(excelData, i, column["身份证号"], &errMsg, 0, 18, "身份证号")
		student.StudentId = validateStudentNumber(excelData, i, column["学号"], &errMsg, 1, 255, "学号")
		score.IsSupplementary = validateEnum(excelData, i, column["是否补测"], &errMsg, true, false, map[string]interface{}{"是": true, "否": false}, "是否补测").(bool)
		score.IsExemption = validateEnum(excelData, i, column["是否免测"], &errMsg, true, false, map[string]interface{}{"是": true, "否": false}, "是否免测").(bool)
		score.Height = validateNullFloat64(excelData, i, column["身高"], &errMsg, 50, 300, false, -1000, "身高")
		score.Weight = validateNullFloat64(excelData, i, column["体重"], &errMsg, 10, 1000, false, -1000, "体重")
		validateBodyMassIndex(score)
		score.VitalCapacity = validateNullFloat64(excelData, i, column["肺活量"], &errMsg, 0, 20000, false, -1000, "肺活量")
		score.FiftyMeters = validateNullFloat64(excelData, i, column["50米跑"], &errMsg, 5, 100, false, -1000, "50米跑")
		score.SitAndReach = validateNullFloat64(excelData, i, column["坐位体前屈"], &errMsg, -100, 100, false, -1000, "坐位体前屈")
		score.ShuttleRun = validateNullMinuteSecond(excelData, i, column["50米×8往返跑"], &errMsg, 40, 600, false, -1000, "50米×8往返跑")
		score.OneMinuteSitUp = validateNullFloat64(excelData, i, column["1分钟仰卧起坐"], &errMsg, 0, 120, false, -1000, "1分钟仰卧起坐")
		score.OneMinuteRopeSkipping = validateNullFloat64(excelData, i, column["1分钟跳绳"], &errMsg, 0, 300, false, -1000, "1分钟跳绳")
		score.StandingLongJump = validateNullFloat64(excelData, i, column["立定跳远"], &errMsg, 0, 400, false, -1000, "立定跳远")
		score.EightHundredMeters = validateNullMinuteSecond(excelData, i, column["800米跑"], &errMsg, 120, 1800, false, -1000, "800米跑")
		score.OneThousandMeters = validateNullMinuteSecond(excelData, i, column["1000米跑"], &errMsg, 120, 1800, false, -1000, "1000米跑")
		score.Chinning = validateNullFloat64(excelData, i, column["引体向上"], &errMsg, 0, 100, false, -1000, "引体向上")
		score.BodyFat = validateNullFloat64(excelData, i, column["体脂百分比"], &errMsg, 0, 100, false, -1000, "体脂百分比")
		score.GripStrength = validateNullFloat64(excelData, i, column["握力"], &errMsg, 0, 100, false, -1000, "握力")
		score.ResponseCapability = validateNullFloat64(excelData, i, column["反应能力"], &errMsg, 0, 10, false, -1000, "反应能力")
		score.BalanceAbility = validateNullFloat64(excelData, i, column["平衡能力"], &errMsg, 0, 100, false, -1000, "平衡能力")
		score.VerticalJump = validateNullFloat64(excelData, i, column["纵跳"], &errMsg, 0, 100, false, -1000, "纵跳")
		score.Chinese = validateNullFloat64(excelData, i, column["语文"], &errMsg, 0, 200, false, -1000, "语文")
		score.Math = validateNullFloat64(excelData, i, column["数学"], &errMsg, 0, 200, false, -1000, "数学")
		score.English = validateNullFloat64(excelData, i, column["英语"], &errMsg, 0, 200, false, -1000, "英语")
		score.Politics = validateNullFloat64(excelData, i, column["政治"], &errMsg, 0, 200, false, -1000, "政治")
		score.History = validateNullFloat64(excelData, i, column["历史"], &errMsg, 0, 200, false, -1000, "历史")
		score.Geography = validateNullFloat64(excelData, i, column["地理"], &errMsg, 0, 200, false, -1000, "地理")
		score.Physics = validateNullFloat64(excelData, i, column["物理"], &errMsg, 0, 200, false, -1000, "物理")
		score.Chemistry = validateNullFloat64(excelData, i, column["化学"], &errMsg, 0, 200, false, -1000, "化学")
		score.Biology = validateNullFloat64(excelData, i, column["生物"], &errMsg, 0, 200, false, -1000, "生物")
		score.Science = validateNullFloat64(excelData, i, column["科学"], &errMsg, 0, 200, false, -1000, "科学")
		validateSame(&sameMap, student.SchoolRollId, i, &errMsg)
		CalculatePhysicalScore(score)
		if len(errMsg) != 0 {
			importMsg.ErrorMsg = append(importMsg.ErrorMsg, errMsg...)
			continue
		}
		importMsg.ValidNum++
		if student.Id == 0 {
			importMsg.InsertStudent++
			createStudent = append(createStudent, student)
			createScore = append(createScore, score)
		} else {
			importMsg.UpdateStudent++
			updateStudent = append(updateStudent, student)
			if score.Id == 0 {
				create = append(create, score)
			} else {
				update = append(update, score)
			}
		}
		if score.Id == 0 {
			importMsg.Insert++
		} else {
			importMsg.Update++
		}
		if score.IsExemption {
			importMsg.ExemptionNum++
		}
		if score.MissingTestNum > 0 {
			importMsg.MissingNum++
		}
	}
	if len(importMsg.ErrorMsg) == 0 {
		importMsg.Result = true
	}
	return
}

func CalculatePhysicalScore(score *entity.Score) {
	calculatePhysicalDetailScore(score, "体重指数", &score.BodyMassIndex, &score.BodyMassIndexBasicScore, &score.BodyMassIndexExtraScore, &score.BodyMassIndexTotalScore, &score.BodyMassIndexLevel, true)
	calculatePhysicalDetailScore(score, "肺活量", &score.VitalCapacity, &score.VitalCapacityBasicScore, &score.VitalCapacityExtraScore, &score.VitalCapacityTotalScore, &score.VitalCapacityLevel, true)
	calculatePhysicalDetailScore(score, "50米跑", &score.FiftyMeters, &score.FiftyMetersBasicScore, &score.FiftyMetersExtraScore, &score.FiftyMetersTotalScore, &score.FiftyMetersLevel, false)
	calculatePhysicalDetailScore(score, "坐位体前屈", &score.SitAndReach, &score.SitAndReachBasicScore, &score.SitAndReachExtraScore, &score.SitAndReachTotalScore, &score.SitAndReachLevel, true)
	calculatePhysicalDetailScore(score, "50米x8往返跑", &score.ShuttleRun, &score.ShuttleRunBasicScore, &score.ShuttleRunExtraScore, &score.ShuttleRunTotalScore, &score.ShuttleRunLevel, false)
	calculatePhysicalDetailScore(score, "一分钟仰卧起坐", &score.OneMinuteSitUp, &score.OneMinuteSitUpBasicScore, &score.OneMinuteSitUpExtraScore, &score.OneMinuteSitUpTotalScore, &score.OneMinuteSitUpLevel, true)
	calculatePhysicalDetailScore(score, "一分钟跳绳", &score.OneMinuteRopeSkipping, &score.OneMinuteRopeSkippingBasicScore, &score.OneMinuteRopeSkippingExtraScore, &score.OneMinuteRopeSkippingTotalScore, &score.OneMinuteRopeSkippingLevel, true)
	calculatePhysicalDetailScore(score, "立定跳远", &score.StandingLongJump, &score.StandingLongJumpBasicScore, &score.StandingLongJumpExtraScore, &score.StandingLongJumpTotalScore, &score.StandingLongJumpLevel, true)
	calculatePhysicalDetailScore(score, "800米跑", &score.EightHundredMeters, &score.EightHundredMetersBasicScore, &score.EightHundredMetersExtraScore, &score.EightHundredMetersTotalScore, &score.EightHundredMetersLevel, false)
	calculatePhysicalDetailScore(score, "1000米跑", &score.OneThousandMeters, &score.OneThousandMetersBasicScore, &score.OneThousandMetersExtraScore, &score.OneThousandMetersTotalScore, &score.OneThousandMetersLevel, false)
	calculatePhysicalDetailScore(score, "引体向上", &score.Chinning, &score.ChinningBasicScore, &score.ChinningExtraScore, &score.ChinningTotalScore, &score.ChinningLevel, true)
	calculateExtraLevel(score, "体脂百分比", &score.BodyFat, &score.BodyFatLevel, true)
	calculateExtraLevel(score, "握力", &score.GripStrength, &score.GripStrengthLevel, true)
	calculateExtraLevel(score, "反应能力", &score.ResponseCapability, &score.ResponseCapabilityLevel, false)
	calculateExtraLevel(score, "平衡能力", &score.BalanceAbility, &score.BalanceAbilityLevel, true)
	calculateExtraLevel(score, "纵跳", &score.VerticalJump, &score.VerticalJumpLevel, true)
	score.PhysicalTotalScore.Float64 = score.PhysicalBasicScore.Float64 + score.PhysicalExtraScore.Float64
	score.PhysicalBasicScore.Valid = true
	score.PhysicalExtraScore.Valid = true
	score.PhysicalTotalScore.Valid = true
	score.PhysicalLevel.Valid = true
	if score.PhysicalTotalScore.Float64 >= 90 {
		score.PhysicalLevel.String = "优秀"
	} else if score.PhysicalTotalScore.Float64 >= 80 {
		score.PhysicalLevel.String = "良好"
	} else if score.PhysicalTotalScore.Float64 >= 60 {
		score.PhysicalLevel.String = "及格"
	} else {
		score.PhysicalLevel.String = "不及格"
	}
}

func calculatePhysicalDetailScore(score *entity.Score, item string, rawValue *sql.NullFloat64, basicScore *sql.NullFloat64, extraScore *sql.NullFloat64, totalScore *sql.NullFloat64, level *sql.NullString, order bool) {
	standard, ok := nspel.PhysicalStandard[item][strconv.FormatInt(int64(score.Sex), 10)][score.Grade]
	if ok {
		basicScore.Valid = true
		extraScore.Valid = true
		totalScore.Valid = true
		level.Valid = true
		if !rawValue.Valid {
			*level = sql.NullString{Valid: true, String: "缺测"}
			score.MissingTestNum++
			return
		}
		weight := standard["权重"].(float64)
		mark := standard["评分"].([]interface{})
		if order {
			for i := 0; i < len(mark); i++ {
				iter := mark[i].(map[string]interface{})
				if rawValue.Float64 >= iter["range"].(float64) {
					basicScore.Float64 = iter["score"].(float64)
					if i == 0 {
						attachMark, ok := standard["加分"]
						if ok {
							attachMark := attachMark.([]interface{})
							mark := rawValue.Float64 - iter["range"].(float64)
							for i := 0; i < len(attachMark); i++ {
								iter := attachMark[i].(map[string]interface{})
								if mark >= iter["range"].(float64) {
									extraScore.Float64 = iter["score"].(float64)
									break
								}
							}
						}
					}
					totalScore.Float64 = basicScore.Float64 + extraScore.Float64
					level.String = iter["level"].(string)
					score.PhysicalBasicScore.Float64 += weight * basicScore.Float64
					score.PhysicalExtraScore.Float64 += extraScore.Float64
					break
				}
			}
		} else {
			for i := 0; i < len(mark); i++ {
				iter := mark[i].(map[string]interface{})
				if rawValue.Float64 <= iter["range"].(float64) {
					basicScore.Float64 = iter["score"].(float64)
					if i == 0 {
						attachMark, ok := standard["加分"]
						if ok {
							attachMark := attachMark.([]interface{})
							mark := rawValue.Float64 - iter["range"].(float64)
							for i := 0; i < len(attachMark); i++ {
								iter := attachMark[i].(map[string]interface{})
								if mark <= iter["range"].(float64) {
									extraScore.Float64 = iter["score"].(float64)
									break
								}
							}
						}
					}
					totalScore.Float64 = basicScore.Float64 + extraScore.Float64
					level.String = iter["level"].(string)
					score.PhysicalBasicScore.Float64 += weight * basicScore.Float64
					score.PhysicalExtraScore.Float64 += extraScore.Float64
					break
				}
			}
		}
	}
}

func calculateExtraLevel(score *entity.Score, item string, rawValue *sql.NullFloat64, level *sql.NullString, order bool) {
	standard, ok := nspel.PhysicalStandard[item][strconv.FormatInt(int64(score.Sex), 10)][score.Grade]
	if ok {
		mark := standard["评分"].([]interface{})
		level.Valid = true
		if !rawValue.Valid {
			level.String = "缺测"
			return
		}
		if order {
			for i := 0; i < len(mark); i++ {
				iter := mark[i].(map[string]interface{})
				if rawValue.Float64 >= iter["range"].(float64) {
					level.String = iter["level"].(string)
					break
				}
			}
		} else {
			for i := 0; i < len(mark); i++ {
				iter := mark[i].(map[string]interface{})
				if rawValue.Float64 <= iter["range"].(float64) {
					level.String = iter["level"].(string)
					break
				}
			}
		}
	}
}

func ScoreImport(school *entity.School, createStudent *[]*entity.Student, createScore *[]*entity.Score, updateStudent *[]*entity.Student, create *[]*entity.Score, update *[]*entity.Score, measurementDate time.Time) error {
	for i := 0; i < len(*createStudent); i++ {
		err := (*createStudent)[i].ImportInsert()
		if err != nil {
			return err
		}
		(*createScore)[i].StudentId = (*createStudent)[i].Id
		(*createScore)[i].MeasurementDate = measurementDate
		err = (*createScore)[i].ImportInsert()
		if err != nil {
			return err
		}
	}
	for i := 0; i < len(*updateStudent); i++ {
		err := (*updateStudent)[i].ImportUpdate()
		if err != nil {
			return err
		}
	}
	for i := 0; i < len(*create); i++ {
		(*create)[i].MeasurementDate = measurementDate
		err := (*create)[i].ImportInsert()
		if err != nil {
			return err
		}
	}
	for i := 0; i < len(*update); i++ {
		(*update)[i].MeasurementDate = measurementDate
		err := (*update)[i].ImportUpdate()
		if err != nil {
			return err
		}
	}
	nspel.RedisKeyDelete("student")
	nspel.RedisKeyDelete("score")
	err := UpdateSchoolScore(school.Id)
	if err != nil {
		return err
	}
	err = UpdateAreaScore(school.ExperimentAreaId)
	if err != nil {
		return err
	}
	return nil
}

func InitReport(school *entity.School, createScore *[]*entity.Score, create *[]*entity.Score, update *[]*entity.Score, measurementDate time.Time) error {
	{
		schoolReport := new(entity.Report)
		err := schoolReport.SelectAllBySchoolIdInfoMeasureTime(school.Id, school.SchoolCode, measurementDate)
		if err != nil {
			if err == orm.ErrNoRows {
				schoolReport.ProvinceId = school.ProvinceId
				schoolReport.CityId = school.CityId
				schoolReport.AreaId = school.AreaId
				schoolReport.ExperimentAreaId = school.ExperimentAreaId
				schoolReport.Type = entity.SchoolTypeReport
				schoolReport.Info = sql.NullString{String: school.SchoolCode, Valid: true}
				schoolReport.Tip = sql.NullString{String: school.Name, Valid: true}
				schoolReport.IsUpdate = true
				schoolReport.MeasureTime = measurementDate
				err = schoolReport.Insert()
				if err != nil {
					return err
				}
			} else {
				return err
			}
		}
	}
	gradeMap := make(map[string]byte)
	classMap := make(map[string]byte)
	for i := 0; i < len(*createScore); i++ {
		gradeMap[(*createScore)[i].Grade] = 1
		classMap[(*createScore)[i].Class] = 1
	}
	for i := 0; i < len(*create); i++ {
		gradeMap[(*create)[i].Grade] = 1
		classMap[(*create)[i].Class] = 1
	}
	for i := 0; i < len(*update); i++ {
		gradeMap[(*update)[i].Grade] = 1
		classMap[(*update)[i].Class] = 1
	}
	for k := range gradeMap {
		gradeReport := new(entity.Report)
		err := gradeReport.SelectAllBySchoolIdInfoMeasureTime(school.Id, k, measurementDate)
		if err != nil {
			if err == orm.ErrNoRows {
				gradeReport.ProvinceId = school.ProvinceId
				gradeReport.CityId = school.CityId
				gradeReport.AreaId = school.AreaId
				gradeReport.ExperimentAreaId = school.ExperimentAreaId
				gradeReport.Type = entity.GradeTypeReport
				gradeReport.Info = sql.NullString{String: k, Valid: true}
				gradeReport.Tip = sql.NullString{String: school.Name, Valid: true}
				gradeReport.IsUpdate = true
				gradeReport.MeasureTime = measurementDate
				err = gradeReport.Insert()
				if err != nil {
					return err
				}
			} else {
				return err
			}
		}
	}
	for k := range classMap {
		gradeReport := new(entity.Report)
		err := gradeReport.SelectAllBySchoolIdInfoMeasureTime(school.Id, k, measurementDate)
		if err != nil {
			if err == orm.ErrNoRows {
				gradeReport.ProvinceId = school.ProvinceId
				gradeReport.CityId = school.CityId
				gradeReport.AreaId = school.AreaId
				gradeReport.ExperimentAreaId = school.ExperimentAreaId
				gradeReport.Type = entity.ClassTypeReport
				gradeReport.Info = sql.NullString{String: k, Valid: true}
				gradeReport.Tip = sql.NullString{String: school.Name, Valid: true}
				gradeReport.IsUpdate = true
				gradeReport.MeasureTime = measurementDate
				err = gradeReport.Insert()
				if err != nil {
					return err
				}
			} else {
				return err
			}
		}
	}
	/*
		var totalScore []*entity.Score
		totalScore = append(totalScore, *createScore...)
		totalScore = append(totalScore, *create...)
		totalScore = append(totalScore, *update...)
		for i := 0; i < len(totalScore); i++ {
			personReport := new(entity.Report)
			err := personReport.SelectAllBySchoolIdInfoMeasureTime(school.Id, (totalScore)[i].SchoolRollId, measurementDate)
			if err != nil {
				if err == orm.ErrNoRows {
					student := new(entity.Student)
					err := student.SelectAllBySchoolRollId(totalScore[i].SchoolRollId)
					if err != nil {
						return err
					}
					personReport.ProvinceId = school.ProvinceId
					personReport.CityId = school.CityId
					personReport.AreaId = school.AreaId
					personReport.ExperimentAreaId = school.ExperimentAreaId
					personReport.Type = entity.PersonTypeReport
					personReport.Info = sql.NullString{String: totalScore[i].SchoolRollId, Valid: true}
					personReport.Tip = sql.NullString{String: student.Name, Valid: true}
					personReport.IsUpdate = true
					personReport.MeasureTime = measurementDate
					err = personReport.Insert()
					if err != nil {
						return err
					}
				} else {
					return err
				}
			}
		}*/
	return nil
}
